package org.example.rmsmsapi.dynamicSql;
import org.apache.ibatis.jdbc.SQL;
import org.example.rmsmsapi.model.*;
import org.example.rmsmsapi.model.Req.bankInfo;
import org.example.rmsmsapi.model.Req.dzddetailreq;
import org.example.rmsmsapi.model.Req.liquiReq;
import org.example.rmsmsapi.model.Req.qsdataReq;
import org.example.rmsmsapi.model.Res.insuinfobydateReq;
import org.example.rmsmsapi.model.Res.qsdRes;

public class srmSqlProvider {
    /**获取退役军人信息**/
    public String GetsyspesonInfo(rmspeson r){
return new SQL(){
    {
SELECT("personid,name,sex,idcardNo,a.tpcode, a.stop ,tpname,creatTime,createName,a.upTime,a.upName ");
   FROM("personInfo a with(nolock) ,personType b with(nolock)");
   WHERE("a.tpcode=b.tpcode");
   if(r.getPersonid()!=null){
WHERE("a.personid=#{personid}");
   }
   if(r.getName()!=null){
       WHERE("a.name=#{name}");
   }
   if (r.getSex()!=null){
     WHERE("a.sex=#{sex}");
   }
   if (r.getIdcardNo()!=null){
       WHERE("a.idcardNo=#{idcardNo}");
   }
   if (r.getTpcode()!=null){
       WHERE("a.tpcode=#{tpcode}");
   }
   if (r.getStop()!=null){
       WHERE("a.stop=#{stop}");
   }
   if (r.getTpname()!=null){
       WHERE("b.tpname=#{tpname}");
   }
    }
}.toString();
    }
    /**保存对账单表**/
    public String Inrencli(dzdinfo r){
        return new SQL(){{
            INSERT_INTO("hosdzd");
            if(r.getHosName()!=null){VALUES("hosName","#{hosName}");}
            if(r.getHosId()!=null){VALUES("hosId","#{hosId}");}
            if(r.getDzsj()!=null){VALUES("dzsj","#{dzsj}");}
            if(r.getStartTime()!=null){VALUES("startTime","#{startTime}");}
            if(r.getEndTime()!=null){VALUES("endTime","#{endTime}");}
            if(r.getUserName()!=null){VALUES("userName","#{userName}");}
            if(r.getUserCode()!=null){VALUES("userCode","#{userCode}");}
            if(r.getZfbz()!=null){VALUES("zfbz","#{zfbz}");}
            if(r.getShrybh()!=null){VALUES("shrybh","#{shrybh}");}
            if(r.getShbz()!=null){VALUES("shbz","#{shbz}");}
            if(r.getShryxm()!=null){VALUES("shryxm","#{shryxm}");}
            if(r.getDzje()!=null){VALUES("dzje","#{dzje}");}
        }}.toString();
    }
//    作废对账单
    public String UphosdzCancle(dzdinfo r){
        return new SQL(){
            {
                UPDATE("hosdzd");
                if(r.getZfbz()!=null){
                SET("zfbz=#{zfbz}");}
                if(r.getZfrybh()!=null){SET("zfrybh=#{zfrybh}");}
                if(r.getZfryxm()!=null){SET("zfryxm=#{zfryxm}");}
                if(r.getZfrq()!=null){SET("zfrq=#{zfrq}");}
                if(r.getDzdh()!=null){
                    WHERE("dzdh=#{dzdh}");
                }
                if(r.getUserCode()!=null){
                    WHERE("userCode=#{userCode}");
                }
                if(r.getDzsj()!=null){
                    WHERE("dzsj=#{dzsj}");
                }
                if(r.getHosId()!=null){WHERE("hosId=#{hosId}");
                }

            }
        }.toString();
    }
//保存银行卡信息
    public String SaveBankInfo(bankInfo b){
        return new SQL(){{
      INSERT_INTO("bankInfo");
      if (b.getIdcardNo()!=null){
          VALUES("idcardNo","#{idcardNo}");
      }
      if (b.getName()!=null){
          VALUES("name","#{name}");
      }
      if (b.getBankCardName()!=null){
          VALUES("bankCardName","#{bankCardName}");
      }
      if(b.getBankCardNo()!=null){
          VALUES("bankCardNo","#{bankCardNo}");
      }
      if(b.getPhone()!=null){
          VALUES("phone","#{phone}");
      }
      if (b.getBzms()!=null){
          VALUES("bzms","#{bzms}");
      }
      if (b.getEditUser()!=null){
          VALUES("editUser","#{editUser}");
      }
      if(b.getEditName()!=null){
          VALUES("editName","#{editName}");
      }
      if (b.getEditTime()!=null){
          VALUES("editTime","#{editTime}");
      }
        }}.toString();
    }
 //修改银行卡信息
    public String UpdateBankInfo(bankInfo b){
        return new SQL(){{
       UPDATE("bankInfo");
       if(b.getBzms()!=null){
           SET("bzms=#{bzms}");
       }
       if(b.getPhone()!=null){
           SET("phone=#{phone}");
       }
       if(b.getBankCardName()!=null){
           SET("bankCardName=#{bankCardName}");
       }
       if(b.getBankCardNo()!=null){
           SET("bankCardNo=#{bankCardNo}");
       }
       if(b.getEditUser()!=null){
           SET("editUser=#{editUser}");
       }
        if(b.getEditName()!=null){
            SET("editName=#{editName}");
        }
        if(b.getEditTime()!=null){
            SET("editTime=#{editTime}");
        }
       if(b.getIdcardNo()!=null){
           WHERE("idcardNo=#{idcardNo}");
       }
       if(b.getRowId()!=null){
           WHERE("rowId=#{rowId}");
       }

        }}.toString();
    }
//    作废对账单明细
    public String uprmshosdzd(dzdinfo r){
        return new SQL(){{
            UPDATE("insusecond");
            SET("sfdz='0'");
            WHERE("dzdh=#{dzdh}");
            if(r.getUserCode()!=null){
                WHERE("dzrybh=#{userCode}");
            }

        }}.toString();
    }
    /***对账后更新明细表信息**/
    public String uprmsRencli(dzdinfo r){
        return  new SQL(){
            {
                UPDATE("insusecond");
                if(r.getDzdh()!=null){
                    SET("dzdh=#{dzdh}");
                }
                SET("sfdz='1'");
               if(r.getUserCode()!=null){
                   SET("dzrybh=#{userCode}");
               }
               if(r.getUserName()!=null){
                   SET("dzryxm=#{userName}");
               }
               WHERE(" sfzf<>'1'  and sfdz<>'1'");
               if(r.getUserCode()!=null){
                   WHERE("userCode=#{userCode}");
               }
               if(r.getUserName()!=null){
                   WHERE("userName=#{userName}");
               }
               if(r.getStartTime()!=null){
                   WHERE("createtime>=#{startTime}");
               }
               if(r.getEndTime()!=null){
                   WHERE("createtime<#{endTime}");
               }


            }
        }.toString();
    }
/***更新退役军人信息*/
    public String UprmsInfo(rmspeson r){
return new SQL(){
    {
        UPDATE("personInfo");
        if (r.getName()!=null){
            SET("name=#{name}");
        }
        if (r.getSex()!=null){
            SET("sex=#{sex}");
        }
        if (r.getIdcardNo()!=null){
            SET("idcardNo=#{idcardNo}");
        }
        if (r.getTpcode()!=null){
            SET("tpcode=#{tpcode}");
        }
        if (r.getStop()!=null){
            SET("stop=#{stop}");
        }
        if (r.getCreatTime()!=null){
            SET("creatTime=#{creatTime}");
        }
        if (r.getCreateName()!=null){
            SET("createName=#{createName}");
        }
        if (r.getUpTime()!=null){SET("upTime=#{upTime}");}
        if (r.getUpName()!=null){  SET("upName=#{upName}");}
        WHERE("personid=#{personid}");
    }
}.toString();
    }
    /**新增退役军人信息*/
 public String InsrtmsInfo(rmspeson r){
        return  new SQL(){
            {
                INSERT_INTO("personInfo");
                if (r.getName()!=null){
                    VALUES("name","#{name}");
                }
                if (r.getSex()!=null){VALUES("sex","#{sex}");}
                if (r.getIdcardNo()!=null){VALUES("idcardNo","#{idcardNo}"); }
                if (r.getTpcode()!=null){VALUES("tpcode","#{tpcode}"); }
                if (r.getStop()!=null){VALUES("stop","#{stop}"); }
                if (r.getCreatTime()!=null){VALUES("creatTime","#{creatTime}"); }
                if (r.getCreateName()!=null){VALUES("createName","#{createName}"); }
                if (r.getUpTime()!=null){VALUES("upTime","#{upTime}"); }
                if (r.getUpName()!=null){VALUES("upName","#{upName}"); }
            }
        }.toString();
 }
//  获取对账单信息，通过类型区分
 public String GetdzmxByType(dzdinfo d){
     return new SQL(){
         {SELECT("COUNT(id) bs,CONVERT(DECIMAL(10, 2),SUM(ecbxje)) je,tpname,tpcode ");
           FROM("insusecond nolock ");
           if(d.getUserCode()!=null){
               WHERE("userCode=#{userCode}");
           }
           if(d.getStartTime()!=null){
               WHERE("createtime>=#{startTime}");
           }
           if(d.getEndTime()!=null){
               WHERE("createtime<#{endTime}");
           }
           if(d.getDzdh()!=null){
               WHERE("dzdh=#{dzdh}");
           }
           if(d.getSfdz()!=null){
               WHERE("sfdz=#{sfdz}");
           }
           WHERE("sfzf='0'");
           GROUP_BY(" tpname,tpcode ");
         }
     }.toString();
 }
 /***获取未对账信息**/
 public String GetnotRencinfo(norencque r){
     return new SQL(){
         {
     SELECT("COUNT(id) bs,CONVERT(DECIMAL(10, 2),SUM(ecbxje)) je,tpname,tpcode,userCode,userName ");
     FROM(" insusecond nolock ");
     if(r.getHosid()!=null){
         WHERE("hosid=#{hosid}");
     }
       WHERE("sfdz='0'");
     WHERE("sfzf='0'");
     GROUP_BY(" tpname,tpcode,userCode,userName ");
         }
     }.toString();
 }
/**插入报销表**/
 public String insudoSQLprovider(insudoReq r){
     return new SQL(){
         {
             INSERT_INTO("insusecond");
             if (r.getZje()!=null){
                 VALUES("zje","#{zje}");
             }
             if (r.getYbjsid()!=null){VALUES("ybjsid","#{ybjsid}");}
             if(r.getGrzfhjje()!=null){VALUES("grzfhjje","#{grzfhjje}");}
             if(r.getNrecbzje()!=null){VALUES("nrecbzje","#{nrecbzje}");}
             if(r.getJjzfze()!=null){VALUES("jjzfze","#{jjzfze}");}
             if(r.getHzxm()!=null){VALUES("hzxm","#{hzxm}");}
             if(r.getZyksmc()!=null){VALUES("zyksmc","#{zyksmc}");}
             if(r.getJzid()!=null){VALUES("jzid","#{jzid}");}
             if(r.getZfje()!=null){VALUES("zfje","#{zfje}");}
             if(r.getYlxxzfje()!=null){VALUES("ylxxzfje","#{ylxxzfje}");}
             if(r.getCxjzffy()!=null){VALUES("cxjzffy","#{cxjzffy}");}
             if(r.getTcjjzffwnfy()!=null){VALUES(" tcjjzffwnfy","#{ tcjjzffwnfy}");}
             if(r.getSfrq()!=null){VALUES("sfrq","#{sfrq}");}
             if(r.getYbjbrxm()!=null){VALUES("ybjbrxm","#{ybjbrxm}");}
             if(r.getQfx()!=null){VALUES("qfx","#{qfx}");}
             if(r.getBrnl()!=null){VALUES("brnl","#{brnl}");}
             if(r.getXb()!=null){VALUES("xb","#{xb}");}
             if(r.getRyrq()!=null){VALUES("ryrq","#{ryrq}");}
             if(r.getCyrq()!=null){VALUES("cyrq","#{cyrq}");}
             if(r.getZyts()!=null){VALUES("zyts","#{zyts}");}
             if(r.getSfzh()!=null){VALUES("sfzh","#{sfzh}");}
             if(r.getDjh()!=null){VALUES("djh","#{djh}");}
             if(r.getBrname()!=null){VALUES("brname","#{brname}");}
             if(r.getZyh()!=null){VALUES("zyh","#{zyh}");}
             if(r.getJzh()!=null){VALUES("jzh","#{jzh}");}
             if(r.getCbdbh()!=null){VALUES("cbdbh","#{cbdbh}");}
             if(r.getCbdmc()!=null){VALUES("cbdmc","#{cbdmc}");}
             if(r.getCyqtzd()!=null){VALUES("cyqtzd","#{cyqtzd}");}
             if(r.getCyzyzdmc()!=null){VALUES("cyzyzdmc","#{cyzyzdmc}");}
             if(r.getBxlb()!=null){VALUES("bxlb","#{bxlb}");}
             if(r.getYllb()!=null){VALUES("yllb","#{yllb}");}
             if(r.getTpcode()!=null){VALUES("tpcode","#{tpcode}");}
             if(r.getTpname()!=null){VALUES("tpname","#{tpname}");}
             if(r.getBxbl()!=null){VALUES("bxbl","#{bxbl}");}
             if(r.getSfjsff()!=null){VALUES("sfjsff","#{sfjsff}");}
             if(r.getUserCode()!=null){VALUES("userCode","#{userCode}");}
             if(r.getUserName()!=null){VALUES("userName","#{userName}");}
             if(r.getHosId()!=null){VALUES("hosId","#{hosId}");}
             if(r.getShrybm()!=null){VALUES("shrybm","#{shrybm}");}
             if(r.getShryxm()!=null){VALUES("shryxm","#{shryxm}");}
             if(r.getShsj()!=null){VALUES("shsj","#{shsj}");}
             if(r.getZfrybm()!=null){VALUES("zfrybm","#{zfrybm}");}
             if(r.getZfryxm()!=null){VALUES("zfryxm","#{zfryxm}");}
             if(r.getSfzf()!=null){VALUES("sfzf","#{sfzf}");}
             if(r.getZfsj()!=null){VALUES("zfsj","#{zfsj}");}
             if(r.getEcbxje()!=null){VALUES("ecbxje","#{ecbxje}");}
             if(r.getJbylbx()!=null){VALUES("jbylbx","#{jbylbx}");}
             if(r.getYljz()!=null){VALUES("yljz","#{yljz}");}
             if(r.getGrxjzfje()!=null){VALUES("grxjzfje","#{grxjzfje}");}
             if(r.getGrzhzfje()!=null){VALUES("grzhzfje","#{grzhzfje}");}
             if(r.getJmdbbx()!=null){VALUES("jmdbbx","#{jmdbbx}");}
             if(r.getZgdebc()!=null){VALUES("zgdebc","#{zgdebc}");}
             if(r.getGrzhye()!=null){VALUES("grzhye","#{grzhye}");}
             if(r.getEcbxhzfje()!=null){VALUES("ecbxhzfje","#{ecbxhzfje}");}
             }
     }.toString();
 }
// 修改报销表
 public String upinsuinfopro(insudoReq r){
   return new SQL(){{
       UPDATE("insusecond");
       if(r.getSfzf()!=null){
           SET("sfzf=#{sfzf}");
       }
       if(r.getZfrybm()!=null){SET("zfrybm=#{zfrybm}");}
       if(r.getZfryxm()!=null){SET("zfryxm=#{zfryxm}");}
       if(r.getZfsj()!=null){SET("zfsj=#{zfsj}");}
       if(r.getId()!=null){
           WHERE("id=#{id}");
       }
       if(r.getJzid()!=null){WHERE("jzid=#{jzid}");}
       if(r.getSfzh()!=null){WHERE("sfzh=#{sfzh}");}
   }}.toString();
 }
 /**获取对账单信息**/
 public String Getdzdinfo(dzdinfo d){
   return new SQL(){
     {
         SELECT(" dzdh,hosId,dzsj,startTime,endTime,userName,userCode,zfbz,shrybh,shryxm,shrq,shbz,dzje,hosName,zfrybh,zfryxm,zfrq");
         FROM("hosdzd nolock");
         if(d.getDzdh()!=null){
             WHERE("dzdh=#{dzdh}");
         }
         if(d.getHosId()!=null){
             WHERE("hosId=#{hosId}");
         }
         if(d.getDzsj()!=null){
             WHERE("dzsj=#{dzsj}");
         }
         if(d.getStartTime()!=null){
             WHERE("startTime=#{startTime}");
         }
         if(d.getEndTime()!=null){
             WHERE("endTime=#{endTime}");
         }
         if(d.getUserName()!=null){
             WHERE("userName=#{userName}");
         }
         if(d.getUserCode()!=null){
             WHERE("userCode=#{userCode}");
         }
         if(d.getZfbz()!=null){
                WHERE("zfbz=#{zfbz}");
         }
         if(d.getShrybh()!=null){
                WHERE("shrybh=#{shrybh}");
         }
         if(d.getShryxm()!=null){
                WHERE("shryxm=#{shryxm}");
         }
         if(d.getShrq()!=null){WHERE("shrq=#{shrq}");}
         if(d.getShbz()!=null){WHERE("shbz=#{shbz}");}
         if(d.getDzje()!=null){WHERE("dzje=#{dzje}");}
         if(d.getHosName()!=null){WHERE("hosName=#{hosName}");}
     }
 }.toString();
  }
    /**获取对账单信息**/
    public String Getdzdinfobydzsj(dzdinfo d){
        return new SQL(){
            {
                SELECT(" dzdh,hosId,dzsj,startTime,endTime,userName,userCode,zfbz,shrybh,shryxm,shrq,shbz,dzje,hosName,zfrybh,zfryxm,zfrq");
                FROM("hosdzd nolock");
                if(d.getDzdh()!=null){
                    WHERE("dzdh=#{dzdh}");
                }
                if(d.getHosId()!=null){
                    WHERE("hosId=#{hosId}");
                }

                if(d.getStartTime()!=null){
                    WHERE("dzsj>=#{startTime}");
                }
                if(d.getEndTime()!=null){
                    WHERE("dzsj<=#{endTime}");
                }
                if(d.getUserName()!=null){
                    WHERE("userName=#{userName}");
                }
                if(d.getUserCode()!=null){
                    WHERE("userCode=#{userCode}");
                }
                if(d.getZfbz()!=null){
                    WHERE("zfbz=#{zfbz}");
                }
                if(d.getShrybh()!=null){
                    WHERE("shrybh=#{shrybh}");
                }
                if(d.getShryxm()!=null){
                    WHERE("shryxm=#{shryxm}");
                }
                if(d.getShrq()!=null){WHERE("shrq=#{shrq}");}
                if(d.getShbz()!=null){WHERE("shbz=#{shbz}");}
                if(d.getDzje()!=null){WHERE("dzje=#{dzje}");}
                if(d.getHosName()!=null){WHERE("hosName=#{hosName}");}
            }
        }.toString();
    }
/***通过请求参数按时间进行统计明细*/
public String GetinsoinfoDetail(insuinfobydateReq r){
     return new SQL(){{
         SELECT(" id,zje,ybjsid,grzfhjje,nrecbzje,jjzfze,hzxm,zyksmc,sfjsff,jzid,zje,zfje,ylxxzfje,cxjzffy,tcjjzffwnfy,sfrq,ybjbrxm,qfx,brnl,xb,ryrq,cyrq,zyts,sfzh,djh,brname,zyh,jzh,cbdbh,cbdmc,cyzyzdmc,cyqtzd,bxlb,yllb,tpcode,tpname,bxbl,createtime,shrybm,shryxm,shsj,zfrybm,zfryxm,zfsj,sfzf, jbylbx,yljz,grxjzfje,grzhzfje,jmdbbx,zgdebc,grzhye,a.hosId,userCode,userName,ecbxje,hosName,hosCode,hoslevel,ecbxhzfje,bankCardName,bankCardNo,phone ");
         FROM("insusecond a with(nolock) left join hosInfo b  with(nolock)on a.hosId=b.hosId left join bankInfo c with(nolock) on a.sfzh=c.idcardNo ");
         if(r.getSfzh()!=null){
             WHERE(" sfzh=#{sfzh}");
         }
         if (r.getSfjsff()!=null){
             WHERE(" sfjsff=#{sfjsff}");
         }
         if(r.getTpcode()!=null){
             WHERE("tpcode=#{tpcode}");
         }
         if (r.getStop()!=null){
             WHERE(" a.stop=#{stop}");
         }
         if(r.getSfzf()!=null){
             WHERE(" sfzf=#{sfzf}");
         }
         if (r.getKsrq()!=null){
            WHERE("createtime >=#{ksrq} ");
         }
         if (r.getJsrq()!=null){
             WHERE("createtime<#{jsrq} ");
         }

     }}.toString();
}
 /***通过对账单类型及具体传入参数查询明细报销信息**/
public String getrecinsutpbytm(dzddetailreq r){
    return new SQL(){
        {
            SELECT("id,zje,ybjsid,grzfhjje,nrecbzje,jjzfze,hzxm,zyksmc,sfjsff,jzid,zje,zfje,ylxxzfje,cxjzffy,tcjjzffwnfy,sfrq,ybjbrxm,qfx,brnl,xb,ryrq,cyrq,zyts,sfzh,djh,brname,zyh,jzh,cbdbh,cbdmc,cyzyzdmc,cyqtzd,bxlb,yllb,tpcode,tpname,bxbl,createtime,shrybm,shryxm,shsj,zfrybm,zfryxm,zfsj,sfzf, jbylbx,yljz,grxjzfje,grzhzfje,jmdbbx,zgdebc,grzhye,a.hosId,userCode,userName,ecbxje,hosName,hosCode,hoslevel");
            FROM("insusecond a with(nolock) left join hosInfo b  with(nolock)on a.hosId=b.hosId ");
            if(r.getDzdh()!=null){
                WHERE(" dzdh=#{dzdh}");
            }
            if(r.getUserCode()!=null){
                WHERE("userCode=#{userCode}");
            }
            if(r.getZfbz()!=null){
                WHERE("sfzf=#{zfbz}");
            }
            if(r.getTpcode()!=null){
                WHERE("tpcode=#{tpcode} ");
            }
            if(r.getStartTime()!=null){
                WHERE("createtime>=#{startTime}");
            }
            if(r.getEndTime()!=null){
                WHERE("createtime<#{endTime}");
            }
            if(r.getHosId()!=null){
                WHERE("a.hosId=#{hosId}");
            }
            if(r.getSfdz()!=null){
                    WHERE("sfdz=#{sfdz}");
            }
            if(r.getQszt()!=null){
                WHERE("qszt=#{qszt}");
            }
            if(r.getQsbh()!=null){
                WHERE("qsbh=#{qsbh}");
            }

        }
    }.toString();
}
/****获取清算金额***/
public  String getqsjeinfo(liquiReq r){
    return  new SQL(){
        {
  SELECT(" COUNT(*)bs, SUM(ecbxje)qsje");
  FROM("insusecond NOLOCK");
  WHERE("sfzf='0'");
  if(r.getQsbz()!=null){
   WHERE("qszt=#{qsbz}");
  }
  if(r.getHosId()!=null){
      WHERE("hosId=#{hosId}");
  }
  if (r.getQsbh()!=null){
      WHERE("qsbh=#{qsbh}");
  }
  if(r.getStarttime()!=null){
      WHERE("createtime>=#{starttime}");
  }
  if(r.getEndtime()!=null){
      WHERE("createtime<#{endtime}");
  }
        }
    }.toString();
}
//保存清算表
public String Inserqsd(qsdRes r){
    return new SQL(){
        {INSERT_INTO("qsb");
          if(r.getHosCode()!=null){
              VALUES("hosCode","#{hosCode}");
          }
          if(r.getHoslevel()!=null){
              VALUES("hoslevel","#{hoslevel}");
          }
          if(r.getHosName()!=null){
              VALUES("hosName","#{hosName}");
          }
          if(r.getHosId()!=null){
              VALUES("hosId","#{hosId}");
          }
          if(r.getQsid()!=null){
              VALUES("qsid","#{qsid}");
          }
          if(r.getQsrs()!=null){
              VALUES("qsrs","#{qsrs}");
          }
          if(r.getQsje()!=null){
           VALUES("qsje","#{qsje}");
          }
          if(r.getStarttime()!=null){
              VALUES("starttime","#{starttime}");
          }
      if(r.getEndtime()!=null){
          VALUES("endtime","#{endtime}");
      }
      if(r.getQssqrybm()!=null){
          VALUES("qssqrybm","#{qssqrybm}");
      }
      if(r.getQssqryxm()!=null){
          VALUES("qssqryxm","#{qssqryxm}");
      }
      if(r.getQssqsj()!=null){
          VALUES("qssqsj","#{qssqsj}");
      }
      if(r.getQsshrybm()!=null){
          VALUES("qsshrybm","#{qsshrybm}");
      }
      if(r.getQsshryxm()!=null){
          VALUES("qsshryxm","#{qsshryxm}");
      }
      if(r.getQsshsj()!=null){
          VALUES("qsshsj","#{qsshsj}");
      }
      if(r.getZfbz()!=null){
          VALUES("zfbz","#{zfbz}");
      }
      if(r.getQsbz()!=null){
          VALUES("qsbz","#{qsbz}");
      }
        }
    }.toString();
}
//更新清算表，用于审核和作废清算表
public String upqsbqsinfo(qsdRes r){
    return new SQL(){
        {
            UPDATE("qsb");
            if(r.getQsbz()!=null){
                SET("qsbz=#{qsbz}");
            }
            if(r.getQsshrybm()!=null){
                SET("qsshrybm=#{qsshrybm}");
            }
            if(r.getQssqryxm()!=null){
                SET("qsshryxm=#{qsshryxm}");
            }
            if(r.getQsshsj()!=null){
                SET("qsshsj=#{qsshsj}");
            }
            if(r.getZfbz()!=null){
                SET("zfbz=#{zfbz}");
            }
            if(r.getZfrybm()!=null){
                SET("zfrybm=#{zfrybm}");
            }
            if(r.getZfryxm()!=null){
                SET("zfryxm=#{zfryxm}");
            }
            if(r.getZfsj()!=null){
                SET("zfsj=#{zfsj}");
            }
            if(r.getHosId()!=null){
                WHERE("hosId=#{hosId}");
            }
            if(r.getQsid()!=null){
                WHERE("qsid=#{qsid}");
            }
            if(r.getQssqsj()!=null){
                WHERE("qssqsj=#{qssqsj}");
            }
        }
    }.toString();
}
/****保存清算表时更新明细结算信息反写明细表**/
public String Upqsdatainfo(qsdRes r){
    return  new SQL(){
        {UPDATE("insusecond");
        if(r.getQsbz()!=null){
           SET("qszt=#{qsbz}");
        }
        if(r.getQsid()!=null){
            SET("qsbh=#{qsid}");
        }
        WHERE("sfzf='0'");
        if(r.getStarttime()!=null){
                WHERE("createtime>=#{starttime}");
        }
        if(r.getEndtime()!=null){
            WHERE("createtime<#{endtime}");
        }
        if(r.getHosId()!=null){
            WHERE("hosId=#{hosId}");
        }
        if(r.getOldqszt()!=null){
            WHERE("qszt=#{oldqszt}");
        }
        }
    }.toString();
}


    /****更新清算表时更新明细结算信息反写明细表**/
    public String Upqsdatainfo1(qsdRes r){
        return  new SQL(){
            {UPDATE("insusecond");
                if(r.getQsbz()!=null){
                    SET("qszt=#{qsbz}");
                }
                WHERE("sfzf='0'");
                if(r.getQsid()!=null){
                    WHERE("qsbh=#{qsid}");
                }
                if(r.getHosId()!=null){
                    WHERE("hosId=#{hosId}");
                }
                if(r.getOldqszt()!=null){
                    WHERE("qszt=#{oldqszt}");
                }
            }
        }.toString();
    }
//获取清算信息
public String Getqsxx(qsdRes r){
    return new SQL(){
        {
            SELECT("qsid,hosId,hosCode,hosName,hoslevel,qsrs,qsje,starttime,endtime,qssqrybm,qssqryxm,qssqsj,qsshrybm,qsshryxm,qsshsj,zfbz,qsbz,zfrybm,zfryxm,zfsj");
            FROM("qsb nolock");
            if(r.getHosId()!=null){
              WHERE("hosId=#{hosId}");
            }
            if(r.getHoslevel()!=null){
                WHERE("hoslevel=#{hoslevel}");
            }
            if(r.getQsrs()!=null){
                WHERE("qsrs=#{qsrs}");
            }
            if(r.getQssqsj()!=null){
                WHERE("qssqsj=#{qssqsj}");
            }
            if(r.getQssqrybm()!=null){
                WHERE("qssqrybm=#{qssqrybm}");
            }
            if(r.getZfbz()!=null){
                WHERE("zfbz=#{zfbz}");
            }
            if(r.getQsbz()!=null){
                WHERE("qsbz=#{qsbz}");
            }
            if(r.getQsid()!=null){
                WHERE("qsid=#{qsid}");
            }
        }
    }.toString();
}

    //获取清算信息
    public String Getqsxxlist(qsdRes r){
        return new SQL(){
            {SELECT("qsid,hosId,hosCode,hosName,hoslevel,qsrs,qsje,starttime,endtime,qssqrybm,qssqryxm,qssqsj,qsshrybm,qsshryxm,qsshsj,zfbz,qsbz,zfrybm,zfryxm,zfsj");
                FROM("qsb nolock");
                if(r.getHosId()!=null){
                    WHERE("hosId=#{hosId}");
                }
                if(r.getHoslevel()!=null){
                    WHERE("hoslevel=#{hoslevel}");
                }
                if(r.getQsrs()!=null){
                    WHERE("qsrs=#{qsrs}");
                }

                if(r.getQssqrybm()!=null){
                    WHERE("qssqrybm=#{qssqrybm}");
                }
                if(r.getZfbz()!=null){
                    WHERE("zfbz=#{zfbz}");
                }
                if(r.getQsbz()!=null){
                    WHERE("qsbz=#{qsbz}");
                }
                if(r.getQsid()!=null){
                    WHERE("qsid=#{qsid}");
                }
                if(r.getStarttime()!=null){
                    WHERE("qssqsj>=#{starttime}");
                }
                if(r.getEndtime()!=null){
                    WHERE("qssqsj<=#{endtime}");
                }
            }
        }.toString();
    }
    //获取结算信息
public String Getinsudoinfo(insudoReq r){
     return new SQL(){
         {
             SELECT(" id,zje,ybjsid,grzfhjje,nrecbzje,jjzfze,hzxm,zyksmc,sfjsff,jzid,zje,zfje,ylxxzfje,cxjzffy,tcjjzffwnfy,sfrq,ybjbrxm,qfx,brnl,xb,ryrq,cyrq,zyts,sfzh,djh,brname,zyh,jzh,cbdbh,cbdmc,cyzyzdmc,cyqtzd,bxlb,yllb,tpcode,tpname,bxbl,createtime,shrybm,shryxm,shsj,zfrybm,zfryxm,zfsj,sfzf, jbylbx,yljz,grxjzfje,grzhzfje,jmdbbx,zgdebc,grzhye,a.hosId,userCode,userName,ecbxje,hosName,hosCode,hoslevel,ecbxhzfje,qszt,sfdz,bankCardName,bankCardNo,phone");
             FROM("insusecond a with(nolock) left join hosInfo b  with(nolock)on a.hosId=b.hosId  left join bankInfo c with(nolock) on a.sfzh=c.idcardNo ");
            if(r.getSfzh()!=null){
                WHERE("sfzh=#{sfzh}");
            }
    if(r.getSfzf()!=null){
     WHERE("sfzf=#{sfzf}");
}
if(r.getYbjsid()!=null){
    WHERE("ybjsid=#{ybjsid}");
}
         }
     }.toString();
 }
//获取清算明细信息用于查询清算明细
    public String Getqsinfodetail(qsdataReq r){
        return new SQL(){
            {
                SELECT(" id,zje,ybjsid,grzfhjje,nrecbzje,jjzfze,hzxm,zyksmc,sfjsff,jzid,zje,zfje,ylxxzfje,cxjzffy,tcjjzffwnfy,sfrq,ybjbrxm,qfx,brnl,xb,ryrq,cyrq,zyts,sfzh,djh,brname,zyh,jzh,cbdbh,cbdmc,cyzyzdmc,cyqtzd,bxlb,yllb,tpcode,tpname,bxbl,createtime,shrybm,shryxm,shsj,zfrybm,zfryxm,zfsj,sfzf, jbylbx,yljz,grxjzfje,grzhzfje,jmdbbx,zgdebc,grzhye,hosId,userCode,userName,ecbxje,hosName,hosCode,hoslevel,ecbxhzfje,qszt,qsztms,qsbh,qssqsj,qssqrybm,qssqryxm,qsshrybm,qsshryxm,qsshsj");
                FROM("View_qsmx with(nolock) ");
                if(r.getSfzh()!=null){
                    WHERE("sfzh=#{sfzh}");
                }
                if(r.getQszt()!=null){
  WHERE("qszt=#{qszt}");
                }
                if(r.getQsbh()!=null){
                    WHERE("qsbh=#{qsbh}");
                }
                if(r.getHzxm()!=null){
                    WHERE("hzxm=#{hzxm}");
                }
                if(r.getKsrq()!=null){
                    WHERE("qssqsj>=#{ksrq}");
                }
                if(r.getJsrq()!=null){
                    WHERE("qssqsj<=#{jsrq}");
                }
               if(r.getHosId()!=null){
                   WHERE("hosId=#{hosId}");
               }
            }
        }.toString();
    }
    //获取医院信息
    public String getHosinfo(hosinfo req){
        return new SQL(){
            {
                SELECT("hosId,hosCode,hosName,stop,hoslevel,appid,appsecret,createUserCode,createUserName,createUserTime,upDateUserCode,upDateUserName,upDateUserTime");
                FROM("hosInfo with(nolock)");
                if(req.getHosId()!=null){
                    WHERE("hosId=#{hosId}");
                }
                if(req.getHosCode()!=null){
                    WHERE("hosCode=#{hosCode}");
                }
                if(req.getStop()!=null){
                    WHERE("stop=#{stop}");
                }
                if(req.getHoslevel()!=null){
                    WHERE("hoslevel=#{hoslevel}");
                }
                if(req.getAppid()!=null){
                    WHERE("appid=#{appid}");
                }
                if(req.getAppsecret()!=null){
                    WHERE("appsecret=#{appsecret}");
                }
                if (req.getHosName()!=null){
                    WHERE("hosName=#{hosName}");
                }

            }
        }.toString();
    }
    //保存医院信息
    public String saveHosinfo(hosinfo req){
        return new SQL(){
            {
                INSERT_INTO("hosInfo");
                if(req.getHosId()!=null){
                    VALUES("hosId","#{hosId}");
                }
                if(req.getHosCode()!=null){
                    VALUES("hosCode","#{hosCode}");
                }
                if(req.getStop()!=null){
                    VALUES("stop","#{stop}");
                }
                if(req.getHoslevel()!=null){
                    VALUES("hoslevel","#{hoslevel}");
                }
                if(req.getAppid()!=null){
                    VALUES("appid","#{appid}");
                }
                if(req.getAppsecret()!=null){
                    VALUES("appsecret","#{appsecret}");
                }
                if(req.getHosName()!=null){
                    VALUES("hosName","#{hosName}");
                }
                if(req.getCreateUserCode()!=null){
                    VALUES("createUserCode","#{createUserCode}");
                }
                if(req.getCreateUserName()!=null){
                    VALUES("createUserName","#{createUserName}");
                }
                if(req.getCreateUserTime()!=null){
                    VALUES("createUserTime","#{createUserTime}");
                }
            }
        }.toString();
    }
    //更新医院信息
    public String updateHosinfo(hosinfo req){
        return new SQL(){
            {
                UPDATE("hosInfo");
               if(req.getAppid()!=null){
                   SET("appid=#{appid}");
               }
               if(req.getAppsecret()!=null){
                   SET("appsecret=#{appsecret}");
               }
               if(req.getHosName()!=null){
                   SET("hosName=#{hosName}");
               }
               if(req.getStop()!=null){
                   SET("stop=#{stop}");
               }
               if(req.getHoslevel()!=null){
                   SET("hoslevel=#{hoslevel}");
               }
               if(req.getUpDateUserCode()!=null){
                   SET("upDateUserCode=#{upDateUserCode}");
               }
               if(req.getUpDateUserName()!=null){
                   SET("upDateUserName=#{upDateUserName}");
               }
               if(req.getUpDateUserTime()!=null){
                   SET("upDateUserTime=#{upDateUserTime}");
               }
               WHERE("hosId=#{hosId}");
            }
        }.toString();
    }
//获取角色信息
    public String getRoleInfo(roleModel r){
        return  new SQL(){
            {
            SELECT("roleid,rolename,stop ,createTime,createUserCode,createUserName,upTime,upUserCode,upUserName");
            FROM("role with(nolock)");
            if(r.getRoleid()!=null){
                WHERE("roleid=#{roleid}");
            }
            if(r.getRolename()!=null){
                WHERE("rolename=#{rolename}");
            }
            if(r.getStop()!=null){
                WHERE("stop=#{stop}");
            }
            }
        }.toString();
    }
    //新增角色
    public String addRoleInfo(roleModel r){
        return new SQL(){
            {
                INSERT_INTO("role");
                if(r.getRoleid()!=null){
                   VALUES("roleid","#{roleid}");
                }
                if(r.getRolename()!=null){
                   VALUES("rolename","#{rolename}");
                }
                if(r.getStop()!=null){
                VALUES("stop","#{stop}");
                }
                if(r.getCreateTime()!=null){
                   VALUES("createTime","#{createTime}");
                }
                if(r.getCreateUserCode()!=null){
                    VALUES("createUserCode","#{createUserCode}");
                }
                if(r.getCreateUserName()!=null){
                 VALUES("createUserName","#{createUserName}");
                }
            }
        }.toString();
    }
    /**更新角色*/
    public String upRoleInfo(roleModel r){
        return new SQL(){
            {
                UPDATE("role");
                if(r.getRolename()!=null){
                    SET("rolename=#{rolename}");
                }
                if(r.getStop()!=null){
                    SET("stop=#{stop}");
                }
                if(r.getUpTime()!=null){
                    SET("upTime=#{upTime}");
                }
                if(r.getUpUserCode()!=null){
                    SET("upUserCode=#{upUserCode}");
                }
                if(r.getUpUserName()!=null){
                    SET("upUserName=#{upUserName}");
                }
                if(r.getRoleid()!=null){
                    WHERE("roleid=#{roleid}");
                }
            }
        }.toString();
    }
    //查询用户信息
    public String getUserinfo(userinfoModel r){
        return new SQL(){
            {
                SELECT("userId,userCode,userName,passWord,hosId,userType,createTime,loginTime,stop,roleid,createUserCode,createUserName,updateTime,updateUserCode,updateUserName,hosName,hoslevel,hosCode,rolename");
                FROM("View_user with(nolock)");
                if(r.getUserId()!=null){
                    WHERE("userId=#{userId}");
                }
                if(r.getUserCode()!=null){
                    WHERE("userCode=#{userCode}");
                }
                if(r.getUserName()!=null){
                    WHERE("userName=#{userName}");
                }
                if(r.getStop()!=null){
                    WHERE("stop=#{stop}");
                }
                if (r.getRoleid()!=null){
                    WHERE("roleid=#{roleid}");
                }
            }
        }.toString();
    }
    //新增用户信息
    public String addUserinfo(userinfoModel r){
        return new SQL(){{
            INSERT_INTO("userinfo");
            if(r.getUserCode()!=null){
                VALUES("userCode","#{userCode}");
            }
            if(r.getUserName()!=null){
                VALUES("userName","#{userName}");
            }
            if(r.getUserType()!=null){
                VALUES("userType","#{userType}");
            }
            if(r.getCreateTime()!=null){
                VALUES("createTime","#{createTime}");
            }
            if(r.getPassWord()!=null){
                VALUES("passWord","#{passWord}");
            }
            if(r.getHosId()!=null){
                VALUES("hosId","#{hosId}");
            }
            if(r.getStop()!=null){
                VALUES("stop","#{stop}");
            }
            if(r.getRoleid()!=null){
                VALUES("roleid","#{roleid}");
            }
            if(r.getCreateUserCode()!=null){
                VALUES("createUserCode","#{createUserCode}");
            }
            if(r.getCreateUserName()!=null){
                VALUES("createUserName","#{createUserName}");
            }
        }}.toString();
    }
//    更新用户信息
    public String upUserinfo(userinfoModel r){
        return new SQL(){{
            UPDATE("userinfo");
            if(r.getUserName()!=null){
                SET("userName=#{userName}");
            }
            if(r.getUserType()!=null){
                SET("userType=#{userType}");
            }
            if(r.getStop()!=null){
                SET("stop=#{stop}");
            }
            if(r.getRoleid()!=null){
                SET("roleid=#{roleid}");
            }
            if(r.getHosId()!=null){
                SET("hosId=#{hosId}");
            }
            if(r.getUpdateTime()!=null){
                SET("updateTime=#{updateTime}");
            }
            if(r.getUpdateUserCode()!=null){
                SET("updateUserCode=#{updateUserCode}");
            }
            if(r.getUpdateUserName()!=null){
                SET("updateUserName=#{updateUserName}");
            }
            if(r.getPassWord()!=null){
                SET("passWord=#{passWord}");
            }
            if(r.getUserId()!=null){
                WHERE("userId=#{userId}");
            }
        }}.toString();
    }
/***获取人员类型信息***/
    public String GetPersonTypeinfo(personTypeModel r){
        return new SQL(){{
            SELECT("tyid,tpcode,tpname,bxbl,stop,sfjsffture,bxbl1,createTime,createUserCode,createUserName,upTime,upUserCode,upUserName");
            FROM("personType with(nolock)");
            if(r.getTyid()!=null){
                WHERE("tyid=#{tyid}");
            }
            if(r.getTpcode()!=null){
                WHERE("tpcode=#{tpcode}");
            }
            if(r.getTpname()!=null){
                WHERE("tpname=#{tpname}");
            }
            if(r.getStop()!=null){
                WHERE("stop=#{stop}");
            }
        }}.toString();
    }
 /***增加人员类型**/
    public String AddPersonType(personTypeModel r){
        return new SQL(){{
            INSERT_INTO("personType");
            if(r.getTpcode()!=null){
           VALUES("tpcode","#{tpcode}");
            }
            if(r.getTpname()!=null){
              VALUES("tpname","#{tpname}");
            }
            if(r.getStop()!=null){
       VALUES("stop","#{stop}");
            }
            if(r.getSfjsffture()!=null){
         VALUES("sfjsffture","#{sfjsffture}");
            }
            if(r.getBxbl()!=null){
            VALUES("bxbl","#{bxbl}");
            }
            if(r.getBxbl1()!=null){
                VALUES("bxbl1","#{bxbl1}");
            }
            if(r.getCreateTime()!=null){
                VALUES("createTime","#{createTime}");
            }
            if(r.getCreateUserCode()!=null){
                VALUES("createUserCode","#{createUserCode}");
            }
            if(r.getCreateUserName()!=null){
                VALUES("createUserName","#{createUserName}");
            }
        }}.toString();
    }
/***修改人员类型信息**/
    public String UpdatePersonType(personTypeModel r){
        return new SQL(){{
            UPDATE("personType");
            if(r.getTpname()!=null){
                SET("tpname=#{tpname}");
            }
            if(r.getStop()!=null){
                SET("stop=#{stop}");
            }
            if(r.getSfjsffture()!=null){
                SET("sfjsffture=#{sfjsffture}");
            }
            if(r.getBxbl()!=null){
                SET("bxbl=#{bxbl}");
            }
            if(r.getBxbl1()!=null){
                SET("bxbl1=#{bxbl1}");
            }
            if(r.getUpTime()!=null){
                SET("upTime=#{upTime}");
            }
            if(r.getUpUserCode()!=null){
                SET("upUserCode=#{upUserCode}");
            }
            if(r.getUpUserName()!=null){
                SET("upUserName=#{upUserName}");
            }
            if(r.getTyid()!=null){
                WHERE("tyid=#{tyid}");
            }
            if(r.getTpcode()!=null){
                WHERE("tpcode=#{tpcode}");
            }

        }}.toString();
    }
/***增加权限对应信息**/
    public String InserRcp(RcpModel r){
        return  new SQL(){{
            INSERT_INTO("rcp");
            if(r.getRoleid()!=null){
                VALUES("roleid","#{roleid}");
            }
            if(r.getPerid()!=null){
                VALUES("perid","#{perid}");
            }
            if(r.getOperaCode()!=null){
                VALUES("operaCode","#{operaCode}");
            }
            if(r.getOperaName()!=null){
                VALUES("operaName","#{operaName}");
            }
        }}.toString();
    }
}
